<?php
	require_once dirname(BASEPATH) . '/application/models/local/data/_init_model.php';
	
	class mod_rate_result extends _init_model
	{
		public $RATE_RT_ID;
		public $RATE_RT_CAT_1;
		public $RATE_RT_CAT_2;
		public $RATE_RT_CAT_3;
		public $RATE_RT_CAT_4;
		public $RATE_RT_CAT_5;
		public $RATE_RT_FREQ;
		public $CAF_ID;
		
		function __construct() {
			parent::__construct();
				
			$this->tableName = "RATE_RESULT";
		}
		
		function get_toprate_result($show_number)
		{
			/*$sqlcmd = 'SELECT c.CAF_ID, c.CAF_TITLE, l.CAF_IMG_L_ID';
			$sqlcmd .= ' FROM cafe c, cafe_img_logo l';
			$sqlcmd .= ' WHERE c.CAF_IMG_L_ID = l.CAF_IMG_L_ID';
			$sqlcmd .= ' AND c.CAF_ID IN (';
			$sqlcmd .= ' (SELECT CAF_ID, (RATE_RT_CAT_1 + RATE_RT_CAT_2 + RATE_RT_CAT_3 + RATE_RT_CAT_4 + RATE_RT_CAT_5) AS S_RESULT';
			$sqlcmd .= ' FROM rate_result ORDER BY S_RESULT DESC LIMIT ' . $show_number . '))';*/
			
			$sqlcmd = 'SELECT c.CAF_ID, c.CAF_NAME, c.CAF_TITLE, cafe_img_logo.CAF_IMG_L_FILENAME, (RATE_RT_CAT_1 + RATE_RT_CAT_2 + RATE_RT_CAT_3 + RATE_RT_CAT_4 + RATE_RT_CAT_5) AS S_RESULT';
			$sqlcmd .= ' FROM rate_result r, cafe c';			
			$sqlcmd .= ' LEFT JOIN cafe_img_logo ON c.CAF_IMG_L_ID = cafe_img_logo.CAF_IMG_L_ID';
			$sqlcmd .= ' WHERE c.CAF_ID = r.CAF_ID';
			$sqlcmd .= ' ORDER BY S_RESULT DESC LIMIT ' . $show_number;
			
			$query = $this->db->query($sqlcmd);
			
			print $query->num_rows();
		}
		function get_rate_result($cafe_id)
		{
			$sqlcmd = 'SELECT * FROM ' . $this->tableName;
			$sqlcmd .= ' WHERE CAF_ID = ' . $cafe_id;

			$query = $this->db->query($sqlcmd);			
			if ($query->num_rows() > 0)
			{
				$row = $query->row(0);								
				$score_overall = ($row->RATE_RT_CAT_1 + $row->RATE_RT_CAT_2 + $row->RATE_RT_CAT_3 + 
									$row->RATE_RT_CAT_4 + $row->RATE_RT_CAT_5) / 5; 									
				return array("SCORE_T1" => number_format(round($row->RATE_RT_CAT_1, 2), 2),
							 "SCORE_T2" => number_format(round($row->RATE_RT_CAT_2, 2), 2),
							 "SCORE_T3" => number_format(round($row->RATE_RT_CAT_3, 2), 2),
							 "SCORE_T4" => number_format(round($row->RATE_RT_CAT_4, 2), 2),
							 "SCORE_T5" => number_format(round($row->RATE_RT_CAT_5, 2), 2),
							 "SCORE_OVERALL" => round($score_overall, 2));
			}
			else 
				return array("SCORE_T1" => 0.00,
							 "SCORE_T2" => 0.00,
							 "SCORE_T3" => 0.00,
							 "SCORE_T4" => 0.00,
							 "SCORE_T5" => 0.00,
							 "SCORE_OVERALL" => 0.00);
		}
		
		function get_top_cafe($order='DESC', $province_id=-1) {			
			
			// Primary query
			$sqlcmd = 'SELECT TABLE_SCORE.CAF_ID, TABLE_SCORE.CAF_NAME, TABLE_SCORE.CAF_TITLE, TABLE_SCORE.CAF_INFO,';
			$sqlcmd .= ' TABLE_SCORE.CAF_P_L_ADDRESS, TABLE_SCORE.PROVINCE_ID, TABLE_SCORE.PROVINCE_NAME,';
			$sqlcmd .= ' TABLE_SCORE.CAF_IMG_L_FILENAME,';
			$sqlcmd .= ' (rate_result.RATE_RT_CAT_1 + rate_result.RATE_RT_CAT_2 + rate_result.RATE_RT_CAT_3 + rate_result.RATE_RT_CAT_4 + rate_result.RATE_RT_CAT_5) / rate_result.RATE_RT_FREQ AS T_SCORE';
			$sqlcmd .= ' FROM rate_result';		
			
				$sqlcmd .= '(SELECT TABLE_CAFE.CAF_ID, TABLE_CAFE.CAF_NAME, TABLE_CAFE.CAF_TITLE, TABLE_CAFE.CAF_INFO,';
				$sqlcmd .= ' TABLE_CAFE.CAF_P_L_ADDRESS, TABLE_CAFE.PROVINCE_ID, TABLE_CAFE.PROVINCE_NAME,';
				$sqlcmd .= ' cafe_img_logo.CAF_IMG_L_FILENAME';							
				$sqlcmd .= ' FROM cafe_img_logo';
					
					$sqlcmd .= ' RIGHT JOIN (SELECT cafe.CAF_ID, cafe.CAF_NAME, cafe.CAF_TITLE, cafe.CAF_INFO, cafe.CAF_IMG_L_ID,';
					$sqlcmd .= ' cafe.CAF_STATUS, cafe.CAF_LAST_ACTIVE,';						
					$sqlcmd .= ' TABLE_ADDRESS.CAF_P_L_ADDRESS, TABLE_ADDRESS.PROVINCE_ID, TABLE_ADDRESS.PROVINCE_NAME';			
					$sqlcmd .= ' FROM cafe';					
								
						$sqlcmd .= ' LEFT JOIN (SELECT cafe_address.CAF_ID, cafe_address.CAF_P_L_ADDRESS,';
						$sqlcmd .= ' province.PROVINCE_ID, province.PROVINCE_NAME';
						$sqlcmd .= ' FROM cafe_address, province';
						$sqlcmd .= ' WHERE cafe_address.PROVINCE_ID = province.PROVINCE_ID) AS TABLE_ADDRESS';
									
						$sqlcmd .= ' ON cafe.CAF_ID = TABLE_ADDRESS.CAF_ID) AS TABLE_CAFE';
						
					$sqlcmd .= ' ON cafe_img_logo.CAF_IMG_L_ID = TABLE_CAFE.CAF_IMG_L_ID';
					$sqlcmd .= ' WHERE TABLE_CAFE.CAF_STATUS = 1';
						
					// Search Option query		
					if ($province_id > -1)
					{
						$sqlcmd .= ' AND TABLE_CAFE.CAF_ID IN';
						$sqlcmd .= ' (SELECT CAF_ID FROM cafe_address';
						$sqlcmd .= ' WHERE PROVINCE_ID = ' . $province_id . ')';
					}				
					$sqlcmd .= ' ORDER BY TABLE_CAFE.CAF_LAST_ACTIVE DESC';
				
				// find result count			
				$count_query = $this->db->query($sqlcmd);
				$count_result = $count_query->num_rows();		
				
				$sqlcmd .= ') AS TABLE_SCORE';
			
			$sqlcmd .= ' WHERE TABLE_SCORE.CAF_ID = rate_result.CAF_ID';
			$sqlcmd .= ' ORDER BY T_SCORE ' . $order;
			$sqlcmd .= ' LIMIT ' . $start_index . ',' . $length;
									
			$query = $this->db->query($sqlcmd);
			
			$return = array();
			if ($query->num_rows() > 0)
			{
				foreach ($query->result() as $row)
				{
					$this->load->library('define/directory_path');
					$title = '-';
					$info = '-';
					$address = '-';					
					$province_name = '-';
					$logo_path = $this->directory_path->img_no_logo_path;
					
					if (!is_null($row->CAF_TITLE) || $row->CAF_TITLE != '')
						$title = $row->CAF_TITLE;
					if (!is_null($row->CAF_INFO) || $row->CAF_INFO != '')
						$info = $row->CAF_INFO;
					if (!is_null($row->CAF_P_L_ADDRESS) || $row->CAF_P_L_ADDRESS != '')
						$address = $row->CAF_P_L_ADDRESS;
					if (!is_null($row->PROVINCE_NAME) || $row->PROVINCE_NAME != '')
						$province_name = $row->PROVINCE_NAME;
					if (!is_null($row->CAF_IMG_L_FILENAME) || $row->CAF_IMG_L_FILENAME != '')
						$logo_path = $this->directory_path->img_logo_path . $row->CAF_IMG_L_FILENAME;
					
					array_push($return, array('CAFE_NAME'		=> $row->CAF_NAME,
												'CAFE_TITLE'	=> $title,												
												'CAFE_INFO'		=> substr(strip_tags($info), 0, 200),
												'CAFE_ADDRESS'	=> $address,												
												'PROVINCE_NAME' => $province_name,
												'LOGO_IMAGE'	=> $logo_path,
												'T_SCORE'		=> $row->T_SCORE											
												));	
				}			
			}
			
			
			$l_return = array('result' => $return,
								'num_rows' => $count_result);
			return $l_return;
		}
	}
?>